package be.phl.model;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
	public static final String KEY_ROWID = "_id";
	protected static final String TAG = "DBAdapter";
	protected static final String DATABASE_NAME = "obstacle_db";
	protected static final int DATABASE_VERSION = 30;
	/**
	 * Array which contains all the table names
	 */
	private static final String[] DATABASE_TABLES = { "User", "UserFunction",
			"City", "BuildingType", "Building", "BuildingSpec",
			"BuildingDetail", "Patient", "MaritalStatus",
			"MovementRestriction", "UsedAid", "PatientSpec", "PatientUser",
			"PatientDetail", "PatientMovementRestriction", "PatientUsedAid",
			"Screening", "ScreeningUser", "RoomType", "RoomItem",
			"ItemProblem", "ScreeningRoom", "ScreeningItem",
			"ScreeningProblem", "PictureItem" };

	/**
	 * Array which contains all the create statements
	 */
	private static final String[] DATABASE_CREATE = {
			// Table to store user data
			"create table User (_id integer primary key, UserName text not null, FirstName text not null, LastName text not null, Password text not null, Email text not null, Function integer, DayOfBirth text, Admin text not null, Activated text not null);",
			"create table UserFunction (_id integer primary key, Name text not null);",
			// Tables needed for building
			"create table City (_id integer primary key, Zip text not null, City text not null);",
			"create table BuildingType (_id integer primary key, Name text not null);",
			"create table Building (_id integer primary key, Street text not null, Number text not null, CityId integer, YearOfConstruction text, BuildingTypeId integer);",
			"create table BuildingSpec (_id integer primary key, BuildingKey text not null, Activated text not null, Comment text, Required text);",
			"create table BuildingDetail (_id integer primary key autoincrement, CentralId integer, BuildingId integer not null, buildingSpecId integer not null, Value text not null);",
			// Tables needed for patient
			"create table Patient (_id integer primary key, FirstName text not null, LastName text not null, Gender text not null, DayOfBirth text not null, Weight integer, Length integer, BuildingId integer not null,  MaritalStatus integer,"
					+ "TelNumber text, Email text, ContactFirstName text, ContactLastName text, ContactRelation text, ContactTelNumber text, ContactEmail text);",
			"create table MaritalStatus (_id integer primary key, Name text not null);",
			"create table MovementRestriction (_id integer primary key, MRKey text not null, Activated text not null);",
			"create table UsedAid (_id integer primary key, Name text not null, Activated text not null, Type text);",
			"create table PatientSpec (_id integer primary key, patientKey text not null, Activated text not null, Comment text, Required text);",
			"create table PatientUser (_id integer primary key autoincrement, patientId integer not null, UserId integer not null);",
			"create table PatientDetail (_id integer primary key autoincrement, CentralId integer, patientId integer not null, patientSpecId integer not null, Value text not null);",
			"create table PatientMovementRestriction (_id integer primary key autoincrement, CentralId integer, patientId integer not null, MovementRestrictionId integer not null, PatientUsedAidId integer, Value text not null);",
			"create table PatientUsedAid (_id integer primary key autoincrement, CentralId integer, patientId integer not null, UsedAidId integer not null);",
			// Tables needed or screening
			"create table Screening (_id integer primary key autoincrement, CentralId integer, PatientId integer not null, BuildingId integer not null, Date text not null, Comment text, Finished text not null);",
			"create table ScreeningUser (_id integer primary key autoincrement, UserId integer not null, ScreeningId integer not null);",
			// Tables needed for screening template
			"create table RoomType (_id integer primary key, Name text not null, Activated text not null);",
			"create table RoomItem (_id integer primary key, RoomTypeId integer, Name text not null, Activated text not null);",
			"create table ItemProblem (_id integer primary key, RoomItemId integer, ParentId integer not null, Name text not null, HasValue text not null, Activated text not null, SingleSelection text);",

			// Tables needed to store the screening data
			// these table have an incrementing PK
			"create table ScreeningRoom (_id integer primary key autoincrement, ScreeningId integer not null, RoomTypeId integer not null, Comment text, Name text not null, ReferenceNumber integer not null, FloorNumber integer);",
			"create table ScreeningItem (_id integer primary key autoincrement, ScreeningRoomId integer not null, RoomItemId integer not null, Comment text, Name text, ReferenceNumber integer);",
			"create table ScreeningProblem (_id integer primary key autoincrement, ScreeningItemId integer not null, ItemProblemId integer not null, value text);",
			"create table PictureItem (_id integer primary key autoincrement, ScreeningItemId integer not null, Path string);" };

	private final Context context;

	private DatabaseHelper DBHelper;
	protected SQLiteDatabase db;

	public DBAdapter(Context ctx) {
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			for (String s : DATABASE_CREATE) {
				db.execSQL(s);
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			for (String s : DATABASE_TABLES) {
				db.execSQL("DROP TABLE IF EXISTS " + s);
			}
			onCreate(db);
		}
	}

	// ---opens the database---
	public DBAdapter open() throws SQLException {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	// ---closes the database---
	public void close() {
		DBHelper.close();
	}
}
